package musictrackrecommendation;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;

import simulation.CumulativeDistributionFunction;

public abstract class DatabaseDataset implements Dataset {
	public static final String DATABASE_CONFIG_FILE_PATH = "database_config.ini";

	public abstract String getJdbcDriverName();

	public abstract String getJdbcConnectionUrl();

	public abstract String getDatabaseUser();

	public abstract String getPassword();

	public abstract String getUserUserTableName();

	public abstract String getUserTrackTableName();

	public abstract String getUserTrackTrainName();

	public abstract String getUserTrackQuizName();

	public abstract String getTrackTrackTableName();

	public abstract String getUserTableName();

	public abstract String getUserIdAttribute();

	public abstract CumulativeDistributionFunction getUserSimilarItems(
			int userId);

	public abstract CumulativeDistributionFunction getTrackSimilarItems(
			int trackId);

	/**
	 * @param tableName
	 *            - interesting table
	 * @return - count of rows in table
	 */
	protected int getItemsCount(String tableName) {
		int count = 0;

		try {
			Class.forName(getJdbcDriverName());
			Connection con = DriverManager.getConnection(
					getJdbcConnectionUrl(), getDatabaseUser(), getPassword());

			Statement stmt = con.createStatement();
			String query = "select count(1) from " + tableName;
			ResultSet rs = stmt.executeQuery(query);

			rs.next();
			count = rs.getInt(1);

			rs.close();
			con.close();
		} catch (Exception e) {
			System.out.print(e.getMessage());
		}

		return count;
	}

	/**
	 * 
	 * @param tableName
	 *            - name of interesting table
	 * @param idAttributeName
	 *            - name of ID attribute
	 * @return maximal value of idAttributeName in tableName
	 */
	protected int getMaximalId(String tableName, String idAttributeName) {
		int maximalId = 0;

		try {
			Class.forName(getJdbcDriverName());
			Connection con = DriverManager.getConnection(
					getJdbcConnectionUrl(), getDatabaseUser(), getPassword());

			Statement stmt = con.createStatement();
			String query = "select max(" + idAttributeName + ") from "
					+ tableName;
			ResultSet rs = stmt.executeQuery(query);

			rs.next();
			maximalId = rs.getInt(1);

			rs.close();
			con.close();
		} catch (Exception e) {
			e.getMessage();
		}

		return maximalId;
	}

	public int[] getUserList() {
		int[] users = new int[getUsersCount()];

		try {
			Class.forName(getJdbcDriverName());
			Connection con = DriverManager.getConnection(
					getJdbcConnectionUrl(), getDatabaseUser(), getPassword());

			Statement stmt = con.createStatement();
			String query = "select " + getUserIdAttribute() + " from "
					+ getUserTableName();
			ResultSet rs = stmt.executeQuery(query);

			int i = 0;
			while (rs.next()) {
				users[i] = rs.getInt(1);
				i++;
			}
			rs.close();
			con.close();
		} catch (Exception e) {
			e.getMessage();
		}

		return users;
	}

	public ArrayList<Integer> getSimilarUsers(int userId, int usersLimit) {

		ArrayList<Integer> similarUsers = new ArrayList<Integer>();

		try {
			Class.forName(getJdbcDriverName());
			Connection con = DriverManager.getConnection(
					getJdbcConnectionUrl(), getDatabaseUser(), getPassword());

			PreparedStatement pstmt;
			ResultSet rs;

			pstmt = con.prepareStatement(getUnionSimilarUsers());
			pstmt.setInt(1, userId);
			rs = pstmt.executeQuery();

			while (rs.next() && similarUsers.size() < usersLimit) {
				similarUsers.add(rs.getInt(1));
			}
		} catch (Exception e) {
			e.getMessage();
		}

		return similarUsers;
	}

	public HashMap<Integer, Integer> getUserTracks(int userId) {
		HashMap<Integer, Integer> userTracks = new HashMap<Integer, Integer>();
		try {
			Class.forName(getJdbcDriverName());
			Connection con = DriverManager.getConnection(
					getJdbcConnectionUrl(), getDatabaseUser(), getPassword());

			PreparedStatement pstmt;
			ResultSet rs;

			// select all user tracks
			pstmt = con
					.prepareStatement("select distinct track_id, playCount from "
							+ getUserTrackTableName() + " where user_id = ?");
			pstmt.setInt(1, userId);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				userTracks.put(rs.getInt(1), rs.getInt(2));
			}
			rs.close();
			con.close();
		} catch (Exception e) {
			e.getMessage();
		}
		return userTracks;
	}

	public ArrayList<Integer> getUserTrackIds(int userId, int tracksLimit) {

		ArrayList<Integer> userTracks = new ArrayList<Integer>();

		try {
			Class.forName(getJdbcDriverName());
			Connection con = DriverManager.getConnection(
					getJdbcConnectionUrl(), getDatabaseUser(), getPassword());

			PreparedStatement pstmt;
			ResultSet rs;

			pstmt = con
					.prepareStatement("select distinct track_id, playcount from "
							+ getUserTrackTableName()
							+ " where user_id = ? order by playcount desc");
			pstmt.setInt(1, userId);
			rs = pstmt.executeQuery();

			while (rs.next() && userTracks.size() < tracksLimit) {
				userTracks.add(rs.getInt(1));
			}

		} catch (Exception e) {
			System.out.println(e.getMessage());
		}

		return userTracks;
	}

	public ArrayList<Integer> getTrackTopUsers(ArrayList<Integer> trackIds,
			int usersLimit) {
		ArrayList<Integer> topUsers = new ArrayList<Integer>();

		try {
			Class.forName(getJdbcDriverName());
			Connection con = DriverManager.getConnection(
					getJdbcConnectionUrl(), getDatabaseUser(), getPassword());

			Statement stmt;
			ResultSet rs;

			stmt = con.createStatement();
			String query = "select " + "TOP " + usersLimit * trackIds.size()
					+ " user_id, playcount from " + getUserTrackTableName()
					+ " where track_id IN "
					+ Utils.getCommaSeparatedString(trackIds)
					+ " order by playcount desc";
			rs = stmt.executeQuery(query);

			while (rs.next() && topUsers.size() < usersLimit * trackIds.size()) {
				topUsers.add(rs.getInt(1));
			}

		} catch (Exception e) {
			System.out.println(e.getMessage());
		}

		return topUsers;
	}

	public ArrayList<Integer> getUserTrackIds(ArrayList<Integer> userIds,
			int tracksLimit) {

		ArrayList<Integer> userTracks = new ArrayList<Integer>();

		try {
			Class.forName(getJdbcDriverName());
			Connection con = DriverManager.getConnection(
					getJdbcConnectionUrl(), getDatabaseUser(), getPassword());

			Statement stmt;
			ResultSet rs;

			stmt = con.createStatement();
			String query = "select " + "TOP " + tracksLimit * userIds.size()
					+ " track_id, playcount from " + getUserTrackTableName()
					+ " where user_id IN "
					+ Utils.getCommaSeparatedString(userIds)
					+ " order by playcount desc";
			rs = stmt.executeQuery(query);

			while (rs.next()
					&& userTracks.size() < tracksLimit * userIds.size()) {
				userTracks.add(rs.getInt(1));
			}

		} catch (Exception e) {
			System.out.println(e.getMessage());
		}

		return userTracks;
	}

	public ArrayList<Integer> getSimilarTracks(int trackId, int tracksLimit) {
		ArrayList<Integer> similarTracks = new ArrayList<Integer>();

		try {
			Class.forName(getJdbcDriverName());
			Connection con = DriverManager.getConnection(
					getJdbcConnectionUrl(), getDatabaseUser(), getPassword());

			PreparedStatement pstmt;
			ResultSet rs;

			pstmt = con.prepareStatement(getUnionSimilarTracks());
			pstmt.setInt(1, trackId);
			rs = pstmt.executeQuery();

			while (rs.next() && similarTracks.size() < tracksLimit) {
				similarTracks.add(rs.getInt(1));
			}
		} catch (Exception e) {
			e.getMessage();
		}

		return similarTracks;
	}

	public ArrayList<Integer> getSimilarTracks(ArrayList<Integer> trackIds,
			int tracksLimit) {
		ArrayList<Integer> similarTracks = new ArrayList<Integer>();

		try {
			Class.forName(getJdbcDriverName());
			Connection con = DriverManager.getConnection(
					getJdbcConnectionUrl(), getDatabaseUser(), getPassword());

			Statement stmt = con.createStatement();
			String query = "select " + "TOP " + tracksLimit * trackIds.size()
					+ " track2_id, similarity from " + getTrackTrackTableName()
					+ " where track1_id IN "
					+ Utils.getCommaSeparatedString(trackIds)
					+ " order by similarity desc";
			ResultSet rs = stmt.executeQuery(query);

			while (rs.next()
					&& similarTracks.size() < tracksLimit * trackIds.size()) {
				similarTracks.add(rs.getInt(1));
			}
		} catch (Exception e) {
			e.getMessage();
		}

		return similarTracks;
	}

	public ArrayList<Integer> getQuizUserTracks(int userId) {
		ArrayList<Integer> quizUserTracks = new ArrayList<Integer>();

		try {
			Class.forName(getJdbcDriverName());
			Connection con = DriverManager.getConnection(
					getJdbcConnectionUrl(), getDatabaseUser(), getPassword());

			PreparedStatement pstmt;
			ResultSet rs;

			pstmt = con.prepareStatement("select distinct track_id from "
					+ getUserTrackQuizName() + " where user_id = ?");
			pstmt.setInt(1, userId);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				quizUserTracks.add(rs.getInt(1));
			}

		} catch (Exception e) {
			System.out.println(e.getMessage());
		}

		return quizUserTracks;
	}

	public ArrayList<Integer> getTrainUserTracks(int userId) {
		ArrayList<Integer> quizUserTracks = new ArrayList<Integer>();

		try {
			Class.forName(getJdbcDriverName());
			Connection con = DriverManager.getConnection(
					getJdbcConnectionUrl(), getDatabaseUser(), getPassword());

			PreparedStatement pstmt;
			ResultSet rs;

			pstmt = con.prepareStatement("select distinct track_id from "
					+ getUserTrackTrainName() + " where user_id = ?");
			pstmt.setInt(1, userId);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				quizUserTracks.add(rs.getInt(1));
			}

		} catch (Exception e) {
			System.out.println(e.getMessage());
		}

		return quizUserTracks;
	}

	private String getUnionSimilarTracks() {
		String query = "select distinct track2_id, similarity from "
				+ getTrackTrackTableName()
				+ " where track1_id = ? order by similarity desc";
		return query;
	}

	private String getUnionSimilarUsers() {
		String query = "select distinct user2_id, similarity from "
				+ getUserUserTableName()
				+ " where user1_id = ? order by similarity desc";
		return query;
	}

}
